package com.junzhiit.nengji.shop.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.CouponSearchBO;
import com.junzhiit.nengji.shop.bo.CouponsDetailsBO;
import com.junzhiit.nengji.shop.bo.UserCouponsInfoBO;
import com.junzhiit.nengji.shop.dao.CouponsDAO;
import com.junzhiit.nengji.shop.model.Coupons;
import com.junzhiit.nengji.shop.model.enums.CouponStatus;
import com.junzhiit.nengji.shop.model.enums.CouponsGrantType;
import com.junzhiit.nengji.shop.model.enums.CouponsPlatform;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.Status;

@Repository
public class CouponsDAOImpl extends AbstractBaseDAO<Coupons> implements CouponsDAO {
	
	@Override
	public List<CouponsDetailsBO> findAll(String userId,String csId,Integer isChain,int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select c.*,s.name shopName from coupons c left join shop s on c.shop_id = s.id   where find_in_set( c.id,(select ifnull(group_concat(coupons_id),'') from user_coupons ");
		sql.append(" where user_id = ? and status = ? and is_delete = ? and end_time > now() ");
		param.setString(userId);
		param.setInt(Is.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		sql.append(" ) ) <= 0 and ");
		sql.append(" c.under_time > now() and (c.valid_end_time > now() or (c.valid_days is not null  and c.valid_days > ?) ) and c.status = ? and grant_type != ? ");
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(CouponsGrantType.NEW_USER_REGISTRATION.toIntValue());
		sql.append(" and c.is_delete = ? ");
		param.setInt(IsDelete.NO.toIntValue());
		if( isChain == Is.NO.toIntValue() ){
			if(StringUtils.isNotBlank(csId)){
				sql.append(" and c.shop_id = ? ");
				param.setString(csId);
			}
		}else{
			if(StringUtils.isNotBlank(csId)){
				sql.append(" and c.chain_id = ? ");
				param.setString(csId);
			}
		}

		sql.append(" order by  created_time desc ");
		return super.queryForPagingList(sql.toString(), CouponsDetailsBO.class, param,  pageNumber, maxPageRow);
	}
	
	@Override
	public List<CouponsDetailsBO> findAllNewPeopleCoupons(String userId,int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select c.*,s.name shopName from coupons c left join shop s on c.shop_id = s.id   where find_in_set( c.id,(select ifnull(group_concat(coupons_id),'') from user_coupons ");
		sql.append(" where user_id = ? and status = ? and is_delete = ? and end_time > now() ");
		param.setString(userId);
		param.setInt(Is.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		sql.append(" ) ) <= 0 and ");
		sql.append(" c.under_time > now() and  c.status = ?  and grant_type = ? and platform = ? ");
		param.setInt(Is.YES.toIntValue());
		param.setInt(CouponsGrantType.NEW_USER_REGISTRATION.toIntValue());
		param.setInt(CouponsPlatform.INTERNAL.toIntValue());
		sql.append(" and c.is_delete = ? ");
		param.setInt(IsDelete.NO.toIntValue());
		sql.append(" order by  created_time desc ");
		return super.queryForPagingList(sql.toString(), CouponsDetailsBO.class, param,  pageNumber, maxPageRow);
	}

	@Override
	public List<UserCouponsInfoBO> findAllByUser(CouponSearchBO bo,String csId,Integer isChain, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select uc.*,s.name shopName from user_coupons uc left join shop s on uc.shop_id = s.id ");
		sql.append(" where uc.user_id = ? ");
		param.setString(bo.getUserId());
		if (bo.getStatus() == CouponStatus.WAIT.toIntValue()) {
			sql.append(" and ( uc.order_id is null or uc.order_id ='') and (uc.order_number is null or uc.order_number ='') and uc.end_time > now() and  uc.status = ? ");
			param.setInt(CouponStatus.WAIT.toIntValue());
		} else if (bo.getStatus() == CouponStatus.USED.toIntValue()) {
			sql.append(" and (( uc.order_id is not null or uc.order_id <>'') or (uc.order_number is not null or uc.order_number <>''))  and  uc.status = ? ");
			param.setInt(CouponStatus.USED.toIntValue());
		} else if (bo.getStatus() == CouponStatus.EXPIRED.toIntValue()) {
			sql.append(" and ((( uc.order_id is null or uc.order_id ='') and (uc.order_number is null or uc.order_number ='') and uc.end_time < now()) or uc.status = ? ) ");
			param.setInt(Status.INACTIVE.toIntStatus());
		}
		if(bo.getPlatform() != null && bo.getPlatform()>=0){
			sql.append(" and uc.platform = ? ");
			param.setInt(bo.getPlatform());
		}
		//查询用户优惠劵,如果传入shopId,则只查询该shopId和总部下所有的优惠劵信息。否。则查询非自有小程序的优惠劵信息。
		if( isChain == Is.NO.toIntValue() ){
			if(StringUtils.isNotBlank(csId)){
				sql.append(" and uc.shop_id = ? ");
				param.setString(csId);
			}
		}else{
			if(StringUtils.isNotBlank(csId)){
				sql.append(" and uc.chain_id = ? ");
				param.setString(csId);
			}
		}
		
		sql.append(" and uc.is_delete = ? ");
		param.setInt(IsDelete.NO.toIntValue());
		
		switch (bo.getSorting()) {
			case 0: {
				sql.append(" order by  uc.created_time desc ");
				break;
			}
			case 1: {
				sql.append(" order by  uc.created_time asc ");
				break;
			}
			case 2: {
				sql.append(" order by  uc.price desc ");
				break;
			}
			case 3: {
				sql.append(" order by  uc.price asc ");
				break;
			}
			case 4: {
				sql.append(" order by  uc.end_time asc ");
				break;
			}
			case 5: {
				sql.append(" order by  uc.end_time desc ");
				break;
			}
			default: {
				sql.append(" order by  uc.created_time desc ");
				break;
			}
		}
		return super.queryForPagingList(sql.toString(), UserCouponsInfoBO.class, param,  pageNumber, maxPageRow);
	}
	
	@Override
	public int getTotalCount(CouponSearchBO bo) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select count(*) from user_coupons ");
		sql.append(" where user_id = ? ");
		param.setString(bo.getUserId());
		if (bo.getStatus() == CouponStatus.WAIT.toIntValue()) {
			sql.append(" and (order_id is null or order_id ='') and (order_number is null or order_number ='') and end_time > now() and  status = ? ");
			param.setInt(CouponStatus.WAIT.toIntValue());
		} else if (bo.getStatus() == CouponStatus.USED.toIntValue()) {
			sql.append(" and ((order_id is not null or order_id <>'') or (order_number is not null or order_number <>'')) ");
		} else if (bo.getStatus() == CouponStatus.EXPIRED.toIntValue()) {
			sql.append(" and (order_id is null or order_id ='') and (order_number is null or order_number ='') and end_time < now() and  status = ? ");
			param.setInt(CouponStatus.USED.toIntValue());
		}
		
		if(bo.getPlatform() != null && bo.getPlatform()>=0){
			sql.append(" and platform = ? ");
			param.setInt(bo.getPlatform());
		}
		sql.append(" and is_delete = ? ");
		param.setInt(IsDelete.NO.toIntValue());
		
		return super.queryForInt(sql.toString(), param);
	}

	@Override
	public int getTotalCount(String userId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		
		sql.append("select count(*) from coupons c where find_in_set( c.id,(select ifnull(group_concat(coupons_id),'') from user_coupons ");
		sql.append(" where user_id = ? and status = ? and is_delete = ? and end_time > now() ");
		param.setString(userId);
		param.setInt(Is.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		sql.append(" ) ) <= 0 and ");
		sql.append(" c.under_time > now() and  c.status = ? and grant_type != ?  ");
		param.setInt(Is.YES.toIntValue());
		param.setInt(CouponsGrantType.NEW_USER_REGISTRATION.toIntValue());
		sql.append(" and c.is_delete = ? ");
		param.setInt(IsDelete.NO.toIntValue());
		
		return super.queryForInt(sql.toString(),param);
	}

	@Override
	public List<Coupons> findAll(Coupons coupons, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from coupons where is_delete = ? ");

		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		if (StringUtils.isNotBlank(coupons.getShopId())) {
			sql.append(" and shop_id=?");
			param.setString(coupons.getShopId());
		}
		
		if (StringUtils.isNotBlank(coupons.getChainId())) {
			sql.append(" and chain_id=?");
			param.setString(coupons.getChainId());
		}

		if (StringUtils.isNotBlank(coupons.getName())) {
			sql.append(" and name like ?");
			param.setString("%" + coupons.getName() + "%");
		}

		if (coupons.getStatus() != null) {
			sql.append(" and status=?");
			param.setInt(coupons.getStatus());
		}

		if (coupons.getPlatform() != null) {
			sql.append(" and platform=?");
			param.setInt(coupons.getPlatform());
		}

		sql.append(" order by created_time desc ");
		
		return queryForPagingList(sql.toString(), Coupons.class, param, pageNumber, maxPageRow);
	}

	@Override
	public List<Coupons> findAllNotPaging(Coupons coupons) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from coupons where is_delete=? ");

		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		if (StringUtils.isNotBlank(coupons.getShopId())) {
			sql.append(" and shop_id=?");
			param.setString(coupons.getShopId());
		}
		
		if (StringUtils.isNotBlank(coupons.getChainId())) {
			sql.append(" and chain_id=?");
			param.setString(coupons.getChainId());
		}

		if (StringUtils.isNotBlank(coupons.getName())) {
			sql.append(" and name like ?");
			param.setString("%" + coupons.getName() + "%");
		}

		if (coupons.getStatus() != null) {
			sql.append(" and status=?");
			param.setInt(coupons.getStatus());
		}

		if (coupons.getPlatform() != null) {
			sql.append(" and platform=?");
			param.setInt(coupons.getPlatform());
		}
		return super.queryForList(sql.toString(), Coupons.class, param);
	}

	@Override
	public int getTotalCount(Coupons coupons) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from coupons where is_delete = ?");

		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		if (StringUtils.isNotBlank(coupons.getShopId())) {
			sql.append(" and shop_id=?");
			param.setString(coupons.getShopId());
		}
		
		if (StringUtils.isNotBlank(coupons.getChainId())) {
			sql.append(" and chain_id=?");
			param.setString(coupons.getChainId());
		}

		if (StringUtils.isNotBlank(coupons.getName())) {
			sql.append(" and name like ?");
			param.setString("%" + coupons.getName() + "%");
		}

		if (coupons.getStatus() != null) {
			sql.append(" and status=?");
			param.setInt(coupons.getStatus());
		}

		if (coupons.getPlatform() != null) {
			sql.append(" and platform=?");
			param.setInt(coupons.getPlatform());
		}

		return queryForInt(sql.toString(), param);
	}

	@Override
	public List<Coupons> getCouponsList(String csId,Integer isChain) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("select id,name from coupons where status = ? and is_delete =?");
		params.setInt(Is.YES.toIntValue());
		params.setInt(IsDelete.NO.toIntValue());
		if(isChain == Is.NO.toIntValue()){
			sql.append(" and shop_id = ?");
			params.setString(csId);
		}

		if(isChain == Is.YES.toIntValue()){
			sql.append(" and chain_id = ? and is_chain = ?");
			params.setString(csId);
			params.setInt(Is.YES.toIntValue());
		}
		
		return super.queryForList(sql.toString(), Coupons.class, params);
	}

	@Override
	public Coupons findById(String couponsId) {
		String sql = "select * from coupons where id = ?";
		StatementParameter params = new StatementParameter();
		params.setString(couponsId);
		return super.query(sql, Coupons.class, params);

	}

	@Override
	public List<Coupons> getCoupons(Integer status) {
		String sql = "select * from coupons where status = ? and  is_delete =?";
		StatementParameter params = new StatementParameter();
		params.setInt(status);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForList(sql, Coupons.class, params);
	}

	@Override
	public List<CouponsDetailsBO> getUserIdReceiveCouponsByshopId(String userId, String csId,Integer isChain) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select c.*,s.name shopName from coupons c left join shop s on c.shop_id = s.id   where find_in_set( c.id,(select ifnull(group_concat(coupons_id),'') from user_coupons ");
		sql.append(" where user_id = ? and status = ? and is_delete = ? and end_time > now() ");
		param.setString(userId);
		param.setInt(Is.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		sql.append(" ) ) <= 0 ");
		sql.append(" and c.under_time > now() and  c.status = ? ");
		param.setInt(Is.YES.toIntValue());
		sql.append(" and c.is_delete = ? ");
		param.setInt(IsDelete.NO.toIntValue());
		
		
		if( isChain == Is.NO.toIntValue() ){
			if(StringUtils.isNotBlank(csId)){
				sql.append(" and c.shop_id = ? ");
				param.setString(csId);
			}
		}else{
			if(StringUtils.isNotBlank(csId)){
				sql.append(" and c.chain_id = ? ");
				param.setString(csId);
			}
		}
		
		
		sql.append(" order by  created_time desc ");
		return super.queryForList(sql.toString(), CouponsDetailsBO.class, param);
	}

	@Override
	public List<Coupons> getNewPeopleCoupons() {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append("select * from coupons where grant_type = ? and status = ? and is_delete = ?");
		param.setInt(CouponsGrantType.NEW_USER_REGISTRATION.toIntValue());
		param.setInt(CouponStatus.USED.toIntValue());
		param.setInt(Is.NO.toIntValue());
		return super.queryForList(sql.toString(), Coupons.class, param);
	}

	@Override
	public List<Coupons> queryAll(Coupons coupons) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from coupons where 1=1 ");
		StatementParameter param = new StatementParameter();

		if (StringUtils.isNotBlank(coupons.getShopId())) {
			sql.append(" and shop_id=?");
			param.setString(coupons.getShopId());
		}
		
		if (StringUtils.isNotBlank(coupons.getChainId())) {
			sql.append(" and chain_id=?");
			param.setString(coupons.getChainId());
		}

		if (StringUtils.isNotBlank(coupons.getName())) {
			sql.append(" and name like ?");
			param.setString("%" + coupons.getName() + "%");
		}

		if (coupons.getStatus() != null) {
			sql.append(" and status=?");
			param.setInt(coupons.getStatus());
		}

		if (coupons.getPlatform() != null) {
			sql.append(" and platform=?");
			param.setInt(coupons.getPlatform());
		}

		sql.append(" order by created_time desc ");
		
		return queryForList(sql.toString(), Coupons.class,param);
	}

}
